

package com.login.until;

import com.login.config.ProFileConfig;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class OracleUtills {

    public static double dissize(String kkdm1, String kkdm2) {


        Connection conn = DBConuntil.getcon();


        Map<String, Double> info = new HashMap<String, Double>();


        double dislong = 0;
//        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();

            String sql = null;
            sql = String.format("select kkwd as WD, kkjd as JD from kkxx where kkbh in ('%s','%s')", kkdm1, kkdm2);
            System.out.println("***********sql*******:" + sql);
            rs = st.executeQuery(sql);


            int inum = 1;
            while (rs.next()) {

                Double JD = rs.getDouble("JD");
                Double WD = rs.getDouble("WD");

                if (JD==null){

                    JD=0.0;
                }
                if (WD==null){

                    WD=0.0;
                }

                info.put("JD" + inum, JD);
                info.put("WD" + inum, WD);
                inum = inum + 1;
            }

            Double jd1 = info.get("JD1");

            Double wd1 = info.get("WD1");
            Double jd2 = info.get("JD2");

            Double wd2 = info.get("WD2");

            System.out.println("*****jd1******:" + jd1);
            System.out.println("*****wd1******:" + wd1);
            System.out.println("*****jd2******:" + jd2);
            System.out.println("*****wd2******:" + wd2);

            if (jd1 == 0.0 || wd1 == 0.0 || jd2 == 0.0 || wd2 == 0.0) {

                dislong = 0;

            } else {

                dislong = DistanceUtils.getDistance(jd1, wd1, jd2, wd2);
            }


            System.out.println("*****dislong******:" + dislong);

        }  catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }

        return dislong;

    }


    public static void writeora(String ldbh, String ss, String ydzz) {

        Connection conn = DBConnection.getcon(2);


//        10   10-20
        String driver = ProFileConfig.driver;

        String jdbcurl = ProFileConfig.YDZSjdbcurl;
        String username = ProFileConfig.YDZSusername;
        String password = ProFileConfig.YDZSpassword;

        String tablename = ProFileConfig.YDZStab;
//        Connection conn = null;
        Statement statement = null;

        try {
//            Class.forName(driver);
//
//            // Setup the connection
//            conn = DriverManager.getConnection(jdbcurl, username, password);


            // Configure to be Read Only
            statement = conn.createStatement();

            Date date = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String str = sdf.format(date);
//                System.out.println(str);
            // Execute Query
//                String sql = "TRUNCATE TABLE " + T_TABLENAME + " DROP STORAGE";
//            String sql = "update SPARKJOB_INFO set MAXRRN = " + maxRRN + " , COUNT_NUM = " + COUNT_NUM +
//                    " , LAST_DATE=to_date('" + str + "','yyyy-MM-dd HH24:mi:ss')" + " where S_TABLENAME = '" + tableName + "'";

//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

            String sql = null;
            sql = String.format("INSERT INTO YDZS VALUES ('%s','%s','%s','%s')", ldbh, ss, ydzz, str);

            statement.executeUpdate(sql);

//            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
//            return 0;
        } finally {
            if (statement != null) {

                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

//            if (conn != null) {
//
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//
//            }
        }

    }

    public static void writeora2(String ldbh, String ss, String ydzz) {

        Connection conn = DBConuntil.getcon();


//        10   10-20

//        Connection conn = null;
        Statement statement = null;

        try {
//            Class.forName(driver);
//
//            // Setup the connection
//            conn = DriverManager.getConnection(jdbcurl, username, password);


            // Configure to be Read Only
            statement = conn.createStatement();

            Date date = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String str = sdf.format(date);
//                System.out.println(str);
            // Execute Query
//                String sql = "TRUNCATE TABLE " + T_TABLENAME + " DROP STORAGE";
//            String sql = "update SPARKJOB_INFO set MAXRRN = " + maxRRN + " , COUNT_NUM = " + COUNT_NUM +
//                    " , LAST_DATE=to_date('" + str + "','yyyy-MM-dd HH24:mi:ss')" + " where S_TABLENAME = '" + tableName + "'";

//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

            String sql = null;
            sql = String.format("INSERT INTO YDZS2 VALUES ('%s','%s','%s','%s')", ldbh, ss, ydzz, str);

            statement.executeUpdate(sql);

//            return 1;
        } catch (SQLException e) {
            e.printStackTrace();
//            return 0;
        } finally {
            if (statement != null) {

                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }

    }

    public static double realdislongm(String kkdm1, String kkdm2) {

//select substr(v.kkmc,1,instr(v.kkmc,'路',-1)) from v_kkxx v

//        select substr(v.kkmc,1,instr(v.kkmc,'路',-1)) from v_kkxx v where v.kkdm ='3715000049';


//        select  x.xh from v_kkxx v,lkxh x where (v.kkdm = '3715000677' or v.kkdm = '3715000687') and substr(v.kkmc,1,instr(v.kkmc,'路',-1)) = x.lkmc
        Connection conn = DBConuntil.getcon();

        String driver = ProFileConfig.driver;

        String jdbcurl = ProFileConfig.jdbcurl;
        String username = ProFileConfig.username;
        String password = ProFileConfig.password;

        Map<String, String> info = new HashMap<String, String>();

//        Double douldjl=0.0;

        double dislong = 0;
//        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        try {
            Class.forName(driver);

            // Setup the connection
//            conn = DriverManager.getConnection(jdbcurl, username, password);

            // Configure to be Read Only
            st = conn.createStatement();

            // Execute Query
//            String sql = "select * from v_kkxx where KKDM in ('%s','%s')"
            String sql = null;
            String sql2 = null;
//            select * from v_kkxx where (KKMC  like '%东昌路%' or KKMC like '%柳园路%' )and KKDM in ('3715000620','3715000621')
//            sql = String.format("select JD,WD from SKKXX where KKDM in ('%s','%s')", kkdm1, kkdm2);


//             select t.* from LDXX t where  t.ldkk like '%3715000518%' or t.ldkk like '%3715000520%'
//            sql = String.format("select  x.xh from v_kkxx v,lkxh x where (v.kkdm = '%s' or v.kkdm = '%s') and substr(v.kkmc,1,instr(v.kkmc,'路',-1)) = x.lkmc", kkdm1, kkdm2);
//            '%%%s%%' or t.ldkk like '%%%s%%'
//            sql = String.format(" select t.* from LDXX t where  t.ldkk like '%%%s%%' or t.ldkk like '%%%s%%'", kkdm1, kkdm2);
            sql = String.format("select t.lkid,t.lkmc from LDXX t where t.lkkk like '%%%s%%' or t.lkkk like '%%%s%%'", kkdm1, kkdm2);

            System.out.println("***********sql*******:" + sql);
            rs = st.executeQuery(sql);


            int inum = 0;
            while (rs.next()) {

                inum = inum + 1;
                String strrslkid = rs.getString("lkid");
//                Double WD = rs.getDouble("WD");
                info.put("lkid" + inum, strrslkid);
//                info.put("WD" + inum, WD);


                String strrslkmc = rs.getString("lkmc");
                info.put("lkmc" + inum, strrslkmc);


            }


            if (inum == 2) {

                System.out.println("********inum==2  说明卡口在不同路口**********");

                String lkid1 = info.get("lkid1");
                String lkid2 = info.get("lkid2");


                sql2 = String.format("select t.lkjl from lkinfokk t where (t.lkid1 ='%s' or t.lkid1 ='%s') and (t.lkid2 ='%s' or t.lkid2 ='%s')", lkid1, lkid2, lkid1, lkid2);

                System.out.println("***********sql2*******:" + sql2);
                rs2 = st.executeQuery(sql2);


                while (rs2.next()) {

                    dislong = rs2.getDouble("lkjl");

                }

            } else {

                System.out.println("********inum！=2  说明卡口在一个路口，或没查询到路口，或查询出多个路口**********");


            }

            System.out.println("*****dislong******:" + dislong);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

            return 0;

        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs2 != null) {
                try {
                    rs2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        }

        return dislong;

    }

    public static double dissizet(String kkdm1, String kkdm2) {


        Connection conn = DBConnection.getcon(2);

        String driver = ProFileConfig.driver;

        String jdbcurl = ProFileConfig.jdbcurl;
        String username = ProFileConfig.username;
        String password = ProFileConfig.password;

        Map<String, Double> info = new HashMap<String, Double>();


        double dislong = 0;
//        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName(driver);

            // Setup the connection
//            conn = DriverManager.getConnection(jdbcurl, username, password);

            // Configure to be Read Only
            st = conn.createStatement();

            // Execute Query
//            String sql = "select * from v_kkxx where KKDM in ('%s','%s')"
            String sql = null;
//            select * from v_kkxx where (KKMC  like '%东昌路%' or KKMC like '%柳园路%' )and KKDM in ('3715000620','3715000621')
//            sql = String.format("select JD,WD from SKKXX where KKDM in ('%s','%s')", kkdm1, kkdm2);
            sql = String.format("  select t.ldmc from LDXX t where  t.ldkk like '%%%s%%' or t.ldkk like '%%%s%%'", kkdm1, kkdm2);
//            String sql3=""
            System.out.println("***********sql*******:" + sql);
            rs = st.executeQuery(sql);


            int inum = 1;
            while (rs.next()) {
//
//                Double JD = rs.getDouble("JD");
//                Double WD = rs.getDouble("WD");
//                info.put("JD" + inum, JD);
//                info.put("WD" + inum, WD);
//                inum = inum + 1;


                String ld = rs.getString("LDMC");
                System.out.println("*****ld******:" + ld);
            }

//            Double jd1 = info.get("JD1");
//
//            Double wd1 = info.get("WD1");
//            Double jd2 = info.get("JD2");
//
//            Double wd2 = info.get("WD2");
//
//            System.out.println("*****jd1******:" + jd1);
//            System.out.println("*****wd1******:" + wd1);
//            System.out.println("*****jd2******:" + jd2);
//            System.out.println("*****wd2******:" + wd2);
//
//            if (jd1 == 0 || wd1 == 0 || jd2 == 0 || wd2 == 0) {
//
//                dislong = 0;
//
//            } else {
//
//                dislong = DistanceUtils.getDistance(jd1, wd1, jd2, wd2);
//            }


            System.out.println("*****dislong******:" + dislong);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();

            return 0;

        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (st != null) {

                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

//            if (conn != null) {
//
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//
//            }
        }

        return dislong;

    }

//    public static void main(String[] args) {
//
//        Connection conn = DBConuntil.getcon();
//
//
//
////        Connection conn = DBConnection.getehldb();
//        OracleUtills oracleUtills = new OracleUtills();
//        double ls = OracleUtills.dissize("3715001077", "3715010111433");
//
//
////        double ls = OracleUtills.realdislongm("3715000559", "3715000829");
////        double ls = OracleUtills.dissizet("3715000518", "3715000520");
////        for (int i = 1; i < 5; i++) {
//
////            OracleUtills.writeora("3715000653_3715000513" + i, "0.25477513389667844", "拥堵");
////        }
//
////
//        if (conn != null) {
//
//            try {
//                conn.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//
//        }
//
//        System.out.println(ls);
//
//    }
}
